package com.wanmait.shoppingsys.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.wanmait.shoppingsys.util.DbHelper;
import com.wanmait.shoppingsys.util.ManagePager;
import com.wanmait.shoppingsys.vo.City;
import com.wanmait.shoppingsys.vo.Province;
import com.wanmait.shoppingsys.vo.Specialty;
import com.wanmait.shoppingsys.vo.Type;

public class SpecialtyManagePagerDAO {
	
	public int getDataCount() {
		int count=0;
		String sql="select count(*) from specialty";
		Connection conn = DbHelper.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs, ps, conn);
		}
		return count;
	}


	public ManagePager findAll(ManagePager managepager){
		ArrayList<Specialty> specialtys = new ArrayList<Specialty>();
		managepager.setData(specialtys);
		managepager.setRowCount(this.getDataCount());
		
		String sql = "SELECT specialty.*, type.kind as type_kind,city.name as city_name FROM specialty left join type on type.id=specialty.type_id left join city on city.id=specialty.city_id LIMIT ?,?";
		Connection conn = DbHelper.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, (managepager.getPageNum()-1)*managepager.getPageSize());
			ps.setInt(2, managepager.getPageSize());
			rs = ps.executeQuery();
			
			while(rs.next()) {
				Specialty specialty = new Specialty();
				specialty.setId(rs.getInt("id"));
				specialty.setName(rs.getString("name"));
				specialty.setPrice(rs.getDouble("price"));
				specialty.setEnable(rs.getBoolean("enable"));
				
				City city=new City();
				city.setId(rs.getInt("city_id"));
				city.setName(rs.getString("city_name"));
				
				Type type=new Type();
				type.setId(rs.getInt("type_id"));
				type.setKind(rs.getString("type_kind"));
				
				specialty.setCity(city);
				specialty.setType(type);
				specialtys.add(specialty);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs, ps, conn);
		}
		return managepager;
	}
	
	
	
	
	public int getDataCount1(Integer typeId) {
		int count=0;
		String sql="select count(*) from specialty where type_id = ?";
		Connection conn = DbHelper.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1,typeId );
			rs = ps.executeQuery();
			if(rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs, ps, conn);
		}
		return count;
	}
	
	
	public ManagePager findByTypeId(ManagePager managepager){
		ArrayList<Specialty> specialtys = new ArrayList<Specialty>();
		managepager.setData(specialtys);
		managepager.setRowCount(this.getDataCount1(managepager.getTypeId()));
		
		String sql = "SELECT specialty.*, type.kind as type_kind,city.name as city_name FROM specialty left join type on type.id=specialty.type_id left join city on city.id=specialty.city_id where specialty.type_id=? LIMIT ?,?";
		Connection conn = DbHelper.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, managepager.getTypeId());
			ps.setInt(2, (managepager.getPageNum()-1)*managepager.getPageSize());
			ps.setInt(3, managepager.getPageSize());
			rs = ps.executeQuery();
			
			while(rs.next()) {
				Specialty specialty = new Specialty();
				specialty.setId(rs.getInt("id"));
				specialty.setName(rs.getString("name"));
				specialty.setPrice(rs.getDouble("price"));
				
				City city=new City();
				city.setId(rs.getInt("city_id"));
				city.setName(rs.getString("city_name"));
				
				Type type=new Type();
				type.setId(rs.getInt("type_id"));
				type.setKind(rs.getString("type_kind"));
				
				specialty.setCity(city);
				specialty.setType(type);
				specialtys.add(specialty);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs, ps, conn);
		}
		return managepager;
	}
	
	
	
	public int getDataCount2(Integer cityId) {
		int count=0;
		String sql="select count(*) from specialty where city_id = ?";
		Connection conn = DbHelper.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1,cityId );
			rs = ps.executeQuery();
			if(rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs, ps, conn);
		}
		return count;
	}
	
	public ManagePager findByCityId(ManagePager managepager){
		ArrayList<Specialty> specialtys = new ArrayList<Specialty>();
		managepager.setData(specialtys);
		managepager.setRowCount(this.getDataCount2(managepager.getCityId()));
		
		String sql = "SELECT specialty.*, type.kind as type_kind,city.name as city_name FROM specialty left join type on type.id=specialty.type_id left join city on city.id=specialty.city_id where specialty.city_id=? LIMIT ?,?";
		Connection conn = DbHelper.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, managepager.getCityId());
			ps.setInt(2, (managepager.getPageNum()-1)*managepager.getPageSize());
			ps.setInt(3, managepager.getPageSize());
			rs = ps.executeQuery();
			
			while(rs.next()) {
				Specialty specialty = new Specialty();
				specialty.setId(rs.getInt("id"));
				specialty.setName(rs.getString("name"));
				specialty.setPrice(rs.getDouble("price"));
				
				City city=new City();
				city.setId(rs.getInt("city_id"));
				city.setName(rs.getString("city_name"));
				
				Type type=new Type();
				type.setId(rs.getInt("type_id"));
				type.setKind(rs.getString("type_kind"));
				
				specialty.setCity(city);
				specialty.setType(type);
				specialtys.add(specialty);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs, ps, conn);
		}
		return managepager;
	}

}
